package com.example.memorypoint.DateBase;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import androidx.viewpager.widget.ViewPager;

import com.example.memorypoint.activity.MainActivity;
import com.example.memorypoint.entity.Diary;
import com.example.memorypoint.fragment.DiaryFragment;

import java.util.ArrayList;
import java.util.List;

public class DiaryDatabase extends SQLiteOpenHelper {

    private static final String DATABASE_NAME = "DiaryDatabase";
    private static final int DATABASE_VERSION = 1;
    private static final String TABLE_DIARY = "diary";
    private static final String COLUMN_ID = "_id";
    private static final String COLUMN_DATE = "date";
    private static final String COLUMN_TITLE = "title"; // 新增标题列
    private static final String COLUMN_CONTENT = "content";
    private static final String COLUMN_WEATHER = "weather";
    private static final String COLUMN_EMOJI = "emoji";
    private static final String COLUMN_TIME = "time";
    private static final String CREATE_TABLE_DIARY = "CREATE TABLE " + TABLE_DIARY + "("
            + COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
            + COLUMN_DATE + " TEXT NOT NULL,"
            + COLUMN_TITLE + " TEXT,"
            + COLUMN_CONTENT + " TEXT,"
            + COLUMN_WEATHER + " TEXT,"
            + COLUMN_EMOJI + " TEXT,"
            + COLUMN_TIME + " TEXT)";


    public DiaryDatabase(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_TABLE_DIARY);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        if (oldVersion < newVersion) {
            db.execSQL("DROP TABLE IF EXISTS " + TABLE_DIARY);
            onCreate(db);
        }
    }

    /**
     * 插入日期
     * 添加新的 fragment 时调用
     * @param date 日记的日期
     * @param time 日记的时间
     * @return 插入的日记 ID，失败时返回 -1
     */
    public int insertDiary(String date, String time) {
        SQLiteDatabase db = getWritableDatabase();
        db.beginTransaction();
        int rowId = -1; // 默认返回 -1，表示插入失败
        try {
            ContentValues values = new ContentValues();
            values.put(COLUMN_DATE, date);
            values.put(COLUMN_WEATHER, "-1");
            values.put(COLUMN_EMOJI, "-1");
            values.put(COLUMN_CONTENT, "");
            values.put(COLUMN_TITLE, "");
            values.put(COLUMN_TIME, time);

            rowId = (int) db.insert(TABLE_DIARY, null, values); // 获取插入的 ID
            if (rowId != -1) {
                db.setTransactionSuccessful();
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            db.endTransaction();
        }
        return rowId;
    }

    /*
    * 查询表中所有日记
    * */
    public void queryAllDiaries() {
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.query(TABLE_DIARY, null, null, null, null, null, null);

        if (cursor != null) {
            while (cursor.moveToNext()) {
                String id = cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_ID));
                String date = cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_DATE));
                String time = cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_TIME));
                String weather = cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_WEATHER));
                String emoji = cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_EMOJI));
                String title = cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_TITLE));
                String content = cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_CONTENT));

                Log.d("asd", "当前数据库中的日记数据：  "+"id: "+id+"日期: " + date + ", 时间: " + time +
                        ", 天气: " + weather + ", 表情: " + emoji + ", 标题: " + title +
                        ", 内容: " + content);
            }
            cursor.close();
        } else {
            Log.d("asd", "数据库中无数据");
        }
    }
    /**
     * @description
     * @param date
     * @return java.util.List<com.example.memorypoint.entity.Diary>
     * @date 2023/6/5 23:07
     */

    public List<Diary> getDiariesByDate(String date) {
        List<Diary> diaries = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        // 使用SQLite查询具有相同COLUMN_DATE的数据
        String selectQuery = "SELECT * FROM " + TABLE_DIARY + " WHERE " + COLUMN_DATE + " = ?";
        Cursor cursor = db.rawQuery(selectQuery, new String[]{date});
        if (cursor.moveToFirst()) {
            do {
                @SuppressLint("Range") int id = cursor.getInt(cursor.getColumnIndex(COLUMN_ID));
                @SuppressLint("Range") String title = cursor.getString(cursor.getColumnIndex(COLUMN_TITLE));
                @SuppressLint("Range") String content = cursor.getString(cursor.getColumnIndex(COLUMN_CONTENT));
                @SuppressLint("Range") int weather = cursor.getInt(cursor.getColumnIndex(COLUMN_WEATHER));
                @SuppressLint("Range") int emoji = cursor.getInt(cursor.getColumnIndex(COLUMN_EMOJI));
                @SuppressLint("Range") String time = cursor.getString(cursor.getColumnIndex(COLUMN_TIME));
                Diary diary = new Diary(id, date, title, content, weather, emoji, time);
                diaries.add(diary);
            } while (cursor.moveToNext());
        }
        cursor.close();
        db.close();
        return diaries;
    }
//    /**
//     * 根据position返回对应的日期
//     *
//     */
//    @SuppressLint("Range")
//    public String getDate(int position) {
//        SQLiteDatabase db = getReadableDatabase();
//        Cursor cursor = db.query(TABLE_DIARY, new String[]{COLUMN_DATE}, null, null, null, null, COLUMN_DATE + " ASC");
//        String date = "";
//        if (cursor.moveToPosition(position)) {
//            date = cursor.getString(cursor.getColumnIndex(COLUMN_DATE));
//        }
//        cursor.close();
//        return date;
//    }

    /**
     * 根据position返回对应的标题
     *
     * @param position
     * @return
     */
    @SuppressLint("Range")
    public String getTitle(int position) {
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.query(TABLE_DIARY, new String[]{COLUMN_TITLE}, null, null, null, null, COLUMN_TITLE + " ASC");
        String title = "";
        if (cursor.moveToPosition(position)) {
            title = cursor.getString(cursor.getColumnIndex(COLUMN_TITLE));
        }
        cursor.close();
        return title;
    }


    /**
     * 返回保存所有fragment的list
     *
     * @return
     */
    public List<Diary> getDairyList() {
        List<Diary> list = new ArrayList<>();
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.query(TABLE_DIARY, null, null, null, null, null, COLUMN_DATE + " DESC, " + COLUMN_TIME + " DESC");
        if (cursor != null && cursor.moveToFirst()) {
            do {
                @SuppressLint("Range") int id = cursor.getInt(cursor.getColumnIndex(COLUMN_ID));
                @SuppressLint("Range") String date = cursor.getString(cursor.getColumnIndex(COLUMN_DATE));
                @SuppressLint("Range") String title = cursor.getString(cursor.getColumnIndex(COLUMN_TITLE));
                @SuppressLint("Range") String content = cursor.getString(cursor.getColumnIndex(COLUMN_CONTENT));
                @SuppressLint("Range") String weather = cursor.getString(cursor.getColumnIndex(COLUMN_WEATHER));
                @SuppressLint("Range") String emoji = cursor.getString(cursor.getColumnIndex(COLUMN_EMOJI));
                @SuppressLint("Range") String time = cursor.getString(cursor.getColumnIndex(COLUMN_TIME));
                Diary diary = new Diary(id, date, title, content, Integer.parseInt(weather), Integer.parseInt(emoji), time);
                list.add(diary);
            } while (cursor.moveToNext());
            cursor.close();
        }
        db.close();
        return list;
    }

    /**
     * 根据日记的ID返回日记
     *
     * @param id
     * @return
     */
//    public DiaryFragment getDiaryById(int id) {
//        SQLiteDatabase db = getReadableDatabase();
//        Cursor cursor = db.query(TABLE_DIARY, null, COLUMN_ID + "=?", new String[]{String.valueOf(id)}, null, null, null);
//        DiaryFragment diary = null;
//        if (cursor.moveToFirst()) {
//            @SuppressLint("Range") String title = cursor.getString(cursor.getColumnIndex(COLUMN_TITLE));
//            @SuppressLint("Range") String content = cursor.getString(cursor.getColumnIndex(COLUMN_CONTENT));
//            @SuppressLint("Range") String date = cursor.getString(cursor.getColumnIndex(COLUMN_DATE));
//            diary = new DiaryFragment(id, title, content, date);
//        }
//        cursor.close();
//        db.close();
//        return diary;
//    }


    /**
     * 删除日记
     *
     * @param id
     */
    public void deleteDiary(int id) {
        // 删除日记
        SQLiteDatabase db = getWritableDatabase();
        db.delete(TABLE_DIARY, COLUMN_ID + "=?", new String[]{String.valueOf(id)});
        db.close();

        // 更新ViewPager中的数据
        MainActivity activity = MainActivity.getInstance(); // 获取MainActivity实例
        List<DiaryFragment> list = activity.getDiaryList(); // 获取MainActivity中的DiaryFragment列表
        DiaryFragment currentFragment = null;

        // 查找要删除的DiaryFragment
        for (DiaryFragment diaryFragment : list) {
            if (diaryFragment.getId() == id) {
                currentFragment = diaryFragment;
                break;
            }
        }

        // 从列表中移除要删除的DiaryFragment
        if (currentFragment != null) {
            list.remove(currentFragment);
        }
        // 通知适配器数据发生变化
        activity.getDiaryPagerAdapter().notifyDataSetChanged();

        // 更新ViewPager当前显示的页面
        ViewPager viewPager = activity.getViewPager();
        if (viewPager.getCurrentItem() >= list.size()) {
            viewPager.setCurrentItem(list.size() - 1);
        }
    }

    /**
     * 根据id获取标题
     *
     * @param id
     * @return
     */
    @SuppressLint("Range")
    public String getTitleById(int id) {
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.query(TABLE_DIARY, null, COLUMN_ID + "=?", new String[]{String.valueOf(id)}, null, null, null);
        String title = "";
        if (cursor.moveToFirst()) {
            title = cursor.getString(cursor.getColumnIndex(COLUMN_TITLE));
        }
        cursor.close();
        db.close();
        return title;
    }

    /**
     * 根据id获取weather
     *
     * @param id
     * @return
     */
    @SuppressLint("Range")
    public String getWeatherById(int id) {
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.query(TABLE_DIARY, null, COLUMN_ID + "=?", new String[]{String.valueOf(id)}, null, null, null);
        String weather = "";
        if (cursor.moveToFirst()) {
            weather = cursor.getString(cursor.getColumnIndex(COLUMN_WEATHER));
        }
        cursor.close();
        db.close();
        return weather;
    }

    /**
     * 根据id获取Emoji
     *
     * @param id
     * @return
     */
    @SuppressLint("Range")
    public String getEmojiById(int id) {
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.query(TABLE_DIARY, null, COLUMN_ID + "=?", new String[]{String.valueOf(id)}, null, null, null);
        String Emoji = "";
        if (cursor.moveToFirst()) {
            Emoji = cursor.getString(cursor.getColumnIndex(COLUMN_EMOJI));
        }
        cursor.close();
        db.close();
        return Emoji;
    }

    /**
     * 根据id获取日记内容
     *
     * @param id
     * @return
     */
    @SuppressLint("Range")
    public String getContentById(int id) {
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.query(TABLE_DIARY, null, COLUMN_ID + "=?", new String[]{String.valueOf(id)}, null, null, null);
        String content = "";
        if (cursor.moveToFirst()) {
            content = cursor.getString(cursor.getColumnIndex(COLUMN_CONTENT));
        }
        cursor.close();
        db.close();
        return content;
    }

    /**
     * 获取最新日记的 ID
      */
    public int getLatestDiaryId() {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery("SELECT " + COLUMN_ID + " FROM " + TABLE_DIARY + " ORDER BY " + COLUMN_ID + " DESC LIMIT 1", null);
        int latestId = -1;
        if (cursor.moveToFirst()) {
            latestId = cursor.getInt(0);
        }
        cursor.close();
        return latestId;
    }

    /**
     * 根据id获取日记
     *
     * @param id
     * @return
     */
    public Diary getDiaryById(int id) {
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.query(TABLE_DIARY, null, COLUMN_ID + "=?",
                new String[]{String.valueOf(id)}, null, null, null);

        Diary diary = null;
        if (cursor.moveToFirst()) {
            @SuppressLint("Range") String date = cursor.getString(cursor.getColumnIndex(COLUMN_DATE));
            @SuppressLint("Range") String title = cursor.getString(cursor.getColumnIndex(COLUMN_TITLE));
            @SuppressLint("Range") String content = cursor.getString(cursor.getColumnIndex(COLUMN_CONTENT));
            @SuppressLint("Range") int weather = cursor.getInt(cursor.getColumnIndex(COLUMN_WEATHER));
            @SuppressLint("Range") int emoji = cursor.getInt(cursor.getColumnIndex(COLUMN_EMOJI));
            @SuppressLint("Range") String time = cursor.getString(cursor.getColumnIndex(COLUMN_TIME));

            diary = new Diary(id, date, title, content, weather, emoji, time);
        }

        cursor.close();
        db.close();
        return diary;
    }

    /**
     * 根据id获取日记日期
     *
     * @param id
     * @return
     */
    @SuppressLint("Range")
    public String getDateById(int id) {
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.query(TABLE_DIARY, new String[]{COLUMN_DATE}, COLUMN_ID + "=?", new String[]{String.valueOf(id)}, null, null, null);
        String date = "";
        if (cursor.moveToFirst()) {
            date = cursor.getString(cursor.getColumnIndex(COLUMN_DATE));
        }
        cursor.close();
        db.close();
        return date;
    }

    /**
     * 更新日记的内容和标题
     *
     * @param id
     * @return
     */
    @SuppressLint("Range")
    public void updateDiary(int id, String title, String content) {
        SQLiteDatabase db = getWritableDatabase();
        db.beginTransaction();
        Log.d("test", "updateDiary: " + id + ";" + title + ":" + content);
        try {
            ContentValues values = new ContentValues();
            values.put(COLUMN_TITLE, title);
            values.put(COLUMN_CONTENT, content);
            db.update(TABLE_DIARY, values, COLUMN_ID + "=?", new String[]{String.valueOf(id)});
            db.setTransactionSuccessful();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            db.endTransaction();
        }
    }

    /**
     * 更新日记weather
     *
     * @param weather
     * @return
     */
    @SuppressLint("Range")
    public void updateWeather(int id, String weather) {
        SQLiteDatabase db = getWritableDatabase();
        db.beginTransaction();
        Log.d("test", "updateWeather: " + id + ":" + weather);
        try {
            ContentValues values = new ContentValues();
            values.put(COLUMN_WEATHER, weather);
            db.update(TABLE_DIARY, values, COLUMN_ID + "=?", new String[]{String.valueOf(id)});
            db.setTransactionSuccessful();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            db.endTransaction();
        }
    }

    /**
     * 更新日记的emoji
     *
     * @param emoji
     * @return
     */
    @SuppressLint("Range")
    public void updateEmoji(int id, String emoji) {
        SQLiteDatabase db = getWritableDatabase();
        db.beginTransaction();
        Log.d("test", "updateEmoji: " + id + ":" + emoji);
        try {
            ContentValues values = new ContentValues();
            values.put(COLUMN_EMOJI, emoji);
            db.update(TABLE_DIARY, values, COLUMN_ID + "=?", new String[]{String.valueOf(id)});
            db.setTransactionSuccessful();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            db.endTransaction();
        }
    }

}
